﻿#include "stdafx.h"
#include "CourseDB.h"
#include <string>

CourseDB::CourseDB()
{
}
Course CourseDB::selectById(const std::string &id)
{
	Course course;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		res = stmt->executeQuery("SELECT * from course where course_id='" + id + "'");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string cid = res->getString(1);
			string title = res->getString(2);
			string tid = res->getString(3);
			course.setCourseId(cid);
			course.setTitle(title);
			course.setTeacherId(tid);

		}
		delete res;
		delete stmt;
		delete con;
		return course;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}

}
void CourseDB::insert(Course &c)
{
	try {
		Connection *con;
		Statement *stmt;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		stmt->executeUpdate("insert into course values('" + c.getCourseId() + "','" + c.getTitle() + "','" + c.getTeacherId() + "')");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void CourseDB::deleteById(const std::string &id)
{
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		stmt->executeUpdate("DELETE from course where course_id='" + id + "'");

		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

void CourseDB::update(Course &c)
{
	try {
		Connection *con;
		Statement *stmt;
		ResultSet * res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		stmt->executeUpdate("update course set title='" + c.getTitle() + "',teacher_id='" + c.getTeacherId() + "' where course_id='" + c.getCourseId() + "'");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Course> CourseDB::selectAll() {
	list<Course> courses;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		res = stmt->executeQuery("SELECT * from course");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string cid = res->getString(1);
			string t = res->getString(2);
			string tid = res->getString(3);

			Course course;
			course.setCourseId(cid);
			course.setTeacherId(tid);
			course.setTitle(t);

			courses.push_back(course);
		}
		delete res;
		delete stmt;
		delete con;
		return courses;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Course> CourseDB::selectAllByTeacherId(const std::string &tid) {
	list<Course> courses;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		res = stmt->executeQuery("SELECT * from course where teacher_id='" + tid + "'");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string cid = res->getString(1);
			string t = res->getString(2);
			string tid = res->getString(3);

			Course course;
			course.setCourseId(cid);
			course.setTeacherId(tid);
			course.setTitle(t);

			courses.push_back(course);
		}
		delete res;
		delete stmt;
		delete con;
		return courses;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Course> CourseDB::selectByLike(const std::string &i) {
	list<Course> courses;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		res = stmt->executeQuery("SELECT * from course inner join teacher on course.teacher_id=teacher.id where title like '%" + i + "%' or name like '%" + i + "%'");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string cid = res->getString(1);
			string t = res->getString(2);
			string tid = res->getString(3);

			Course course;
			course.setCourseId(cid);
			course.setTeacherId(tid);
			course.setTitle(t);

			courses.push_back(course);
		}
		delete res;
		delete stmt;
		delete con;

		return courses;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Course> CourseDB::selectByLike2(const std::string &i) {//通过课程名字或课程id名字模糊搜索课程
	list<Course> courses;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		res = stmt->executeQuery("SELECT * from course where title like '%" + i + "%' or course_id like '%" + i + "%'");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string cid = res->getString(1);
			string t = res->getString(2);
			string tid = res->getString(3);

			Course course;
			course.setCourseId(cid);
			course.setTeacherId(tid);
			course.setTitle(t);

			courses.push_back(course);
		}
		delete res;
		delete stmt;
		delete con;

		return courses;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

CourseDB::~CourseDB()
{
}
